package com.etooling.framework.repository;


import com.etooling.framework.entity.FuncModule;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface FuncModuleRepository extends BaseRepository<FuncModule> {
    String sql = "" +
            "";//原生sql

    @Query(nativeQuery = true, value = "WITH RECURSIVE a AS ( " +
            "SELECT * " +
            "  FROM framework.t_func_module " +
            "  WHERE id = ?1 " +
            "UNION ALL " +
            "  SELECT d.* " +
            "  FROM framework.t_func_module d " +
            "  JOIN a ON a.id = d.parent_id ) " +
            "SELECT * FROM a  order by a.seq;")
    List<FuncModule> getFuncChildrenByParentId(String rootId);

    @Query(nativeQuery = true, value = "with recursive tmp0 as " +
            "                   ( " +
            "                       SELECT * " +
            "                       FROM framework.t_func_module " +
            "                       where id in ( " +
            "                           SELECT rf.func_id " +
            "                           FROM framework.t_role_func rf " +
            "                           WHERE rf.role_id IN " +
            "                                 (SELECT er.role_id FROM framework.t_employee_role er WHERE er.employee_id =?1) " +
            "                             and id <> 'root' " +
            "                       ) " +
            "                       union " +
            "                       SELECT t1.* " +
            "                       FROM framework.t_func_module t1, " +
            "                            tmp0 t0 " +
            "                       where 1 = 1 " +
            "                         and t1.id = t0.parent_id " +
            "                   ) " +
            "SELECT * " +
            "FROM tmp0 order by seq")
    List<FuncModule> getFuncChildrenByUserId(String userId);


    @Query(nativeQuery = true, value = "with recursive tmp0 as  " +
            "                   (  " +
            "                       SELECT *  " +
            "                       FROM framework.t_func_module  " +
            "                       where id in (  " +
            "                           SELECT rf.func_id  " +
            "                           FROM framework.t_role_func rf  " +
            "                           WHERE rf.role_id IN  " +
            "                                 (select id from framework.t_role where t_role.name = '供应商企业负责人')  " +
            "                             and id <> 'root'  " +
            "                       )  " +
            "                       union  " +
            "                       SELECT t1.*  " +
            "                       FROM framework.t_func_module t1,  " +
            "                            tmp0 t0  " +
            "  " +
            "                       where 1 = 1  " +
            "                         and t1.id = t0.parent_id  " +
            "                   )  " +
            "SELECT *  " +
            "FROM tmp0  " +
            "order by seq")
    List<FuncModule> getFuncChildrenByRoleName(String roleName);

    @Transactional
    @Modifying
    @Query(nativeQuery = true, value = "delete from framework.t_role_func where func_id=?1")
    void deleteFuncnByRoleModul(String id);

    @Query(nativeQuery = true, value = "select * from framework.t_func_module where name = '' ")
    List<FuncModule> getFuncChildrenBySupplierUserId();

    @Query(nativeQuery = true, value = "select * from framework.t_func_module fm" +
            " left join  framework.t_role_func  rf on  rf.func_id=fm.id " +
            " left join framework.t_employee_role  em on em.role_id=rf.role_id   where func_type='功能模块'  " +
            " and em.employee_id=?1  ")
    List<FuncModule> getALLBtnFuncModules(String employeeId);

    @Query(nativeQuery = true, value = "select * from framework.t_func_module where  company_id=?1 and parent_id is null")
    FuncModule getRoot(String companyId);


    @Query(nativeQuery = true, value = "WITH RECURSIVE dict AS ( " +
            "    SELECT * " +
            "    FROM framework.t_func_module " +
            "    WHERE id in (select func_id " +
            "               from framework.t_role_func " +
            "               where role_id in (select role_id " +
            "                                 from framework.t_employee_role " +
            "                                 where employee_id = ?1)) " +
            "    union ALL " +
            "    SELECT framework.t_func_module.* " +
            "    FROM framework.t_func_module, " +
            "         dict " +
            "    WHERE framework.t_func_module.id = dict.parent_id " +
            ") " +
            "SELECT distinct * " +
            "FROM dict " +
            "ORDER BY seq")
    List<FuncModule> getModulesTreeByUserId(String userId);

    @Query(nativeQuery = true, value = "select * from framework.t_func_module  order by parent_id,seq")
    List<FuncModule> getAllFuncModuleList();

    @Query(nativeQuery = true, value = "select coalesce(max(seq),0)  from framework.t_func_module where parent_id=?1 ")
    Integer getMaxSeq(String parentId);

    @Transactional
    @Modifying()
    @Query(nativeQuery = true, value = "update framework.t_func_module  set seq = (case when seq = ?1 then ?2 when seq= ?2 then ?1 end )where seq in (?1,?2) and parent_id=?3 ")
    void updateSeq(Integer oldSeq, Integer newSeq, String parentId);
}
